﻿Imports System.Text.RegularExpressions
Imports System.Data
Imports System.Data.SqlClient
Public Class UsrCtrlHocSinh
    Public Sub LoadLuaChon()
        cbxChon.Items.Add("Tìm theo mã số học sinh")
        cbxChon.Items.Add("Tìm theo tên học sinh")
        cbxChon.Items.Add("Tìm theo quê quán")
        cbxChon.Items.Add("Tìm theo tôn giáo")
        cbxChon.Items.Add("Tìm theo giới tính")
    End Sub
    Public Sub LoadLop()
        Dim HocSinh_BUS As HocSinhBUS = New HocSinhBUS
        Dim sql As String = "select MaLop, TenLop from Lop"
        HocSinh_BUS.LoadCBX(cbxLop, sql)
        cbxLop.DisplayMember = "TenLop"
        cbxLop.ValueMember = "MaLop"
    End Sub
    Public Sub LoadDaTa()
        Dim HocSinh_BUS As HocSinhBUS = New HocSinhBUS
        Dim sql As String = "select MaHocSinh, HoTen, NgaySinh, DiaChi, Email, " &
                            "(CASE when GioiTinh = 1 then N'Nam' else N'Nữ' end) " &
                            "as GioiTinh, QueQuan, TonGiao, MaLop from HOCSINH"
        HocSinh_BUS.LoadDGV(dgvHocSinh, sql)
    End Sub

    Private Sub btnSearchHocSinh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchHocSinh.Click
        Dim HocSinh_BUS As HocSinhBUS = New HocSinhBUS
        If (cbxChon.Text = "Tìm theo mã số học sinh") Then
            Dim sql As String = "select MaHocSinh, HoTen, NgaySinh, DiaChi, Email, " &
                                "(CASE when GioiTinh = 1 then N'Nam' else N'Nữ' end) " &
                                "as GioiTinh, QueQuan, TonGiao, MaLop from HOCSINH" &
                                " Where MaHocSinh Like '%" & txtSearch.Text & "%'"
            HocSinh_BUS.LoadDGV(dgvHocSinh, sql)
        ElseIf (cbxChon.Text = "Tìm theo tên học sinh") Then
            Dim sql As String = "select MaHocSinh, HoTen, NgaySinh, DiaChi, Email, " &
                                "(CASE when GioiTinh = 1 then N'Nam' else N'Nữ' end) " &
                                "as GioiTinh, QueQuan, TonGiao, MaLop from HOCSINH" &
                                " Where HoTen Like '%" & txtSearch.Text & "%'"
            HocSinh_BUS.LoadDGV(dgvHocSinh, sql)
        ElseIf (cbxChon.Text = "Tìm theo quê quán") Then
            Dim sql As String = "select MaHocSinh, HoTen, NgaySinh, DiaChi, Email, " &
                                "(CASE when GioiTinh = 1 then N'Nam' else N'Nữ' end) " &
                                "as GioiTinh, QueQuan, TonGiao, MaLop from HOCSINH" &
                                " Where QueQuan Like '%" & txtSearch.Text & "%'"
            HocSinh_BUS.LoadDGV(dgvHocSinh, sql)
        ElseIf (cbxChon.Text = "Tìm theo tôn giáo") Then
            Dim sql As String = "select MaHocSinh, HoTen, NgaySinh, DiaChi, Email, " &
                                "(CASE when GioiTinh = 1 then N'Nam' else N'Nữ' end) " &
                                "as GioiTinh, QueQuan, TonGiao, MaLop from HOCSINH" &
                                " Where TonGiao Like '%" & txtSearch.Text & "%'"
            HocSinh_BUS.LoadDGV(dgvHocSinh, sql)
        ElseIf (cbxChon.Text = "Tìm theo giới tính") Then
            Dim gt As Integer
            If txtSearch.Text = "Nam" Or txtSearch.Text = "nam" Then
                gt = 1
            ElseIf txtSearch.Text = "Nữ" Or txtSearch.Text = "nữ" Then
                gt = 0
            Else
                MessageBox.Show("Giới tính chỉ là nam hoặc nữ")
            End If
            Dim sql As String = "select MaHocSinh, HoTen, NgaySinh, DiaChi, Email, " &
                                "(CASE when GioiTinh = 1 then N'Nam' else N'Nữ' end) " &
                                "as GioiTinh, QueQuan, TonGiao, MaLop from HOCSINH" &
                                " Where GioiTinh Like '%" & gt & "%'"
            HocSinh_BUS.LoadDGV(dgvHocSinh, sql)
        End If
    End Sub

    Private Sub btnThemHS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThemHS.Click
        Dim HocSinh_BUS As HocSinhBUS = New HocSinhBUS
        Dim hs As HocSinhDTO = New HocSinhDTO
        hs.MaHocSinh = txtMaHocSinh.Text
        hs.HoTen = txtTenHocSinh.Text
        hs.QueQuan = txtQueQuan.Text
        hs.DiaChi = txtDiaChi.Text
        hs.TonGiao = txtTonGiao.Text
        hs.MaLop = cbxLop.SelectedValue
        Dim MaxTuoi As Integer
        Dim MinTuoi As Integer
        Dim Tuoi As Integer
        Dim sql As String = "Select TuoiToiThieu, TuoiToiDa from THAMSO"
        Dim GetDT As DataProvider = New DataProvider
        Dim reader As SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
        If reader.Read() Then
            MinTuoi = reader.GetInt32(0)
            MaxTuoi = reader.GetInt32(1)
        End If
        Tuoi = Date.Now.Year - dtpNgaySinh.Value.Year
        If Tuoi > MinTuoi And Tuoi < MaxTuoi Then
            hs.NgaySinh = dtpNgaySinh.Value
        Else
            MessageBox.Show("Tuổi học sinh từ 15 tới 20 tuổi, mời xem lại")
        End If
        Dim match As String = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
        Dim reg As Regex = New Regex(match)
        If (reg.IsMatch(txtEmail.Text)) Then
            hs.Email = txtEmail.Text
        Else
            MessageBox.Show("Email không hợp lệ, mời xem lại", "Opps!", MessageBoxButtons.OK)
        End If
        If (rbtNam.Checked) Then
            hs.GioiTinh = 1
        ElseIf (rbtNu.Checked) Then
            hs.GioiTinh = 0
        End If
        If (HocSinh_BUS.ThemHocSinh(hs)) Then
            MessageBox.Show("Thêm học sinh mới thành công", "^^", MessageBoxButtons.OK)
            LoadDaTa()
        Else
            MessageBox.Show("Thao tác thất bại, vui lòng xem lại dữ liệu nhập", "Opps!", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub btnXoaHS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoaHS.Click
        Dim HocSinhBUS As HocSinhBUS = New HocSinhBUS
        Dim index As DataGridViewSelectedRowCollection = dgvHocSinh.SelectedRows
        For Each A As DataGridViewRow In index
            Dim MaHocSinh As String = A.Cells(0).Value
            Dim MaLop As String = A.Cells(8).Value
            Dim GetDT As DataProvider = New DataProvider
            Dim sql As String = "Select SiSo from LOP where MaLop = '" & MaLop & "'"
            Dim reader As SqlClient.SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
            Dim ss As Integer
            If reader.Read() Then
                ss = reader.GetInt32("0")
            End If
            If (HocSinhBUS.XoaHocSinh(MaHocSinh) = False) Then
                MessageBox.Show("Xoá không thành công vui lòng kiểm tra lại kết nối mạng !!!", "Opps!", MessageBoxButtons.OK)
                Return
            Else
                ss = ss - 1
                sql = "Update LOP set SiSo = " & ss & " where MaLop = '" & MaLop & "'"
                Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql)
                GetDT.SQL_ExecuteNonQuery(cmd)
            End If
        Next
        MessageBox.Show("Xóa thành công !!!", "^^", MessageBoxButtons.OK)
        LoadDaTa()
        
    End Sub

    Private Sub btnSuaHS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSuaHS.Click
        Dim HocSinh_BUS As HocSinhBUS = New HocSinhBUS
        Dim hs As HocSinhDTO = New HocSinhDTO
        hs.MaHocSinh = txtMaHocSinh.Text
        hs.HoTen = txtTenHocSinh.Text
        hs.QueQuan = txtQueQuan.Text
        hs.DiaChi = txtDiaChi.Text
        hs.TonGiao = txtTonGiao.Text
        hs.MaLop = cbxLop.SelectedValue
        Dim MaxTuoi As Integer
        Dim MinTuoi As Integer
        Dim Tuoi As Integer
        Dim sql As String = "Select TuoiToiThieu, TuoiToiDa from THAMSO"
        Dim GetDT As DataProvider = New DataProvider
        Dim reader As SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
        If reader.Read() Then
            MinTuoi = reader.GetInt32(0)
            MaxTuoi = reader.GetInt32(1)
        End If
        Tuoi = Date.Now.Year - dtpNgaySinh.Value.Year
        If Tuoi > MinTuoi And Tuoi < MaxTuoi Then
            hs.NgaySinh = dtpNgaySinh.Value
        Else
            MessageBox.Show("Tuổi học sinh từ 15 tới 20 tuổi, mời xem lại")
        End If
        Dim match As String = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
        Dim reg As Regex = New Regex(match)
        If (reg.IsMatch(txtEmail.Text)) Then
            hs.Email = txtEmail.Text
        Else
            MessageBox.Show("Email không hợp lệ, mời xem lại", "Opps!", MessageBoxButtons.OK)
        End If
        If (rbtNam.Checked) Then
            hs.GioiTinh = 1
        ElseIf (rbtNu.Checked) Then
            hs.GioiTinh = 0
        End If
        If (HocSinh_BUS.SuaHocSinh(hs)) Then
            MessageBox.Show("Cập nhật học sinh mới thành công", "^^", MessageBoxButtons.OK)
            LoadDaTa()
        Else
            MessageBox.Show("Thao tác thất bại, vui lòng xem lại dữ liệu nhập", "Opps!", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub dgvHocSinh_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgvHocSinh.SelectionChanged
        txtMaHocSinh.Text = dgvHocSinh.CurrentRow.Cells(1).Value
        txtTenHocSinh.Text = dgvHocSinh.CurrentRow.Cells(2).Value
        dtpNgaySinh.Value = dgvHocSinh.CurrentRow.Cells(3).Value
        cbxLop.Text = dgvHocSinh.CurrentRow.Cells(9).Value
        txtDiaChi.Text = dgvHocSinh.CurrentRow.Cells(4).Value
        txtQueQuan.Text = dgvHocSinh.CurrentRow.Cells(7).Value
        txtTonGiao.Text = dgvHocSinh.CurrentRow.Cells(8).Value
        txtEmail.Text = dgvHocSinh.CurrentRow.Cells(5).Value
        If (dgvHocSinh.CurrentRow.Cells(6).Value = "Nam") Then
            rbtNam.Checked = True
        Else
            rbtNu.Checked = True
        End If
    End Sub
    
    Private _MaHocSinh As String
    Public Property MaHocSinh() As String
        Get
            Return _MaHocSinh
        End Get
        Set(ByVal value As String)
            _MaHocSinh = value
        End Set
    End Property

    Private Sub dgvHocSinh_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvHocSinh.CellContentClick
        MaHocSinh = dgvHocSinh.CurrentRow.Cells(1).Value
        Dim frm As frmDTB = New frmDTB()
        'That su cho nay no ko khac gi cua ng h viet l ai ng hiểu:)
        frm.UsrCtrlDiemTrungBinh1.MaHocSinh = MaHocSinh
        frm.Text = MaHocSinh
        frm.ShowDialog()
        Dim HocSinh_BUS As HocSinhBUS = New HocSinhBUS
        Dim sql As String = "select distinct(NamHoc) from DiemTrungBinh"
        HocSinh_BUS.LoadCBX(frm.UsrCtrlDiemTrungBinh1.cbxLuaChon, sql)
        frm.UsrCtrlDiemTrungBinh1.cbxLuaChon.DisplayMember = "NamHoc"
        frm.UsrCtrlDiemTrungBinh1.cbxLuaChon.ValueMember = "NamHoc"
    End Sub
End Class
